Can a range be passed to a query?

I'm still struggling with using ranges... Can they be passed to a query
somehow...

I have this so far but it pulls nothing:

//Show all with $letter not between "A" and "Z"

if ($selectedLetter =3D "#") {

$other =3D range('0','9');

$sql =3D "SELECT DISTINCT ID, name, address FROM restaurants WHERE name LIK=
E
'$other'";
=20
$result =3D mysql_query($sql) or die(mysql_error());

}
=20
while($row =3D mysql_fetch_assoc($result)){

=20

$name =3D $row['name'];

printf(
=20
'',

$row['ID'], =20
=20
$row['name'],
=20
$row['address']
=20
);
=20

}


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Can a range be passed to a query?

On Jul 31, 2009, at 5:24 PM, "Miller, Terion" > wrote:

> I'm still struggling with using ranges... Can they be passed to a
> query
> somehow...
>
> I have this so far but it pulls nothing:
>
> //Show all with $letter not between "A" and "Z"
>
> if ($
>
> $result = mysql_query($sql) or die(mysql_error());
>
> }
>
> while($row = mysql_fetch_assoc($result)){
>
>
>
> $name = $row['name'];
>
> printf(
>
> '',
>
> $row['ID'],
>
> $row['name'],
>
> $row['address']
>
> );
>
>
> }
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


What about

> selectedLetter = "#") {
>
> $other = range('0','9');
>
> $sql = "SELECT DISTINCT ID, name, address FROM restaurants WHERE left
> (name, 1) between 0 and 9";




Bastien

Sent from my iPod

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Can a range be passed to a query?

(Sorry, forgot to send this to the whole list.)

On Fri, Jul 31, 2009 at 05:24:45PM -0400, Miller, Terion wrote:

> I'm still struggling with using ranges... Can they be passed to a query
> somehow...
>
> I have this so far but it pulls nothing:
>
> //Show all with $letter not between "A" and "Z"
>
> if ($selectedLetter = "#") {

Problem #1: The above expression will set $selectedLetter to '#', *not*
check whether $selectedLetter is equal to '#'.

>
> $other = range('0','9');
>
> $sql = "SELECT DISTINCT ID, name, address FROM restaurants WHERE name LIKE
> '$other'";

Problem #2: The range() function returns an array (see documentation).
Echo the $sql variable to screen and you may find that it says:

"SELECT DISTINCT ... LIKE 'Array'"

Try this:

$values = implode(',', $other);
$sql = "SELECT DISTINCT ... LIKE '$values'";

(See documentation for implode().)



Paul

--
Paul M. Foster

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Can a range be passed to a query?

On Fri, Jul 31, 2009 at 5:55 PM, Phpster wrote:
> What about
>
> $sql = "SELECT DISTINCT ID, name, address FROM restaurants WHERE
> left(name, 1) between 0 and 9";
>
>
>
>
> Bastien
>
> Sent from my iPod

You need to wrap the 0 and the 9 in single quotes, or else it returns
everything.

$sql = "SELECT DISTINCT ID, name, address FROM restaurants WHERE
left(name, 1) between '0' and '9'";

Performance could be an issue, also. I tried this and the two versions
I posted on her other thread against a table I have that has just over
8700 rows in it. This version took over 9 times longer to execute.
(Granted, on a table that small it's still 0.0092 seconds compared to
0.0010 seconds. Either of those times is acceptable in my book, but on
a larger table a 9x performance drain could be a serious issue.)
That's because the engine has to examine every row in the table and
calculate left(name, 1) before it can compare that value to the range.
The other versions I posted can simply scan/seek the index within a
range.

Andrew

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Can a range be passed to a query?

On 8/1/09 12:04 AM, "Paul M Foster" wrote:

$values =3D implode(',', $other);

For some reason no matter how I tried to get it to say # should pull anythi=
ng that does not start with a letter.....
It pulls all restaurants....or it breaks the full script.
Here are all the things I have tried so far...

//Create array with letters AND number sign =
$letters =3D range('A','Z'); array_push($letters, '=
#'); $menu =3D ''; =
$selectedLetter =3D isset($_GET['letter']) ? $_GET['letter'] =
: null; foreach($letters as $letter) { =
$menu .=3D ($letter == $selectedLetter) =
? sprintf('%s ', $letter) : sp=
rintf(' ', $letter, $letter);=
} echo "

Re: Can a range be passed to a query?

On 8/1/09 1:47 AM, "Andrew Ballard" wrote:

On Fri, Jul 31, 2009 at 5:55 PM, Phpster wrote:
> What about
>
> $sql =3D "SELECT DISTINCT ID, name, address FROM restaurants WHERE
> left(name, 1) between 0 and 9";
>
>
>
>
> Bastien
>
> Sent from my iPod

You need to wrap the 0 and the 9 in single quotes, or else it returns
everything.

$sql =3D "SELECT DISTINCT ID, name, address FROM restaurants WHERE
left(name, 1) between '0' and '9'";

Performance could be an issue, also. I tried this and the two versions
I posted on her other thread against a table I have that has just over
8700 rows in it. This version took over 9 times longer to execute.
(Granted, on a table that small it's still 0.0092 seconds compared to
0.0010 seconds. Either of those times is acceptable in my book, but on
a larger table a 9x performance drain could be a serious issue.)
That's because the engine has to examine every row in the table and
calculate left(name, 1) before it can compare that value to the range.
The other versions I posted can simply scan/seek the index within a
range.

Andrew


Not sure why none of these are working for me since you have them tested ag=
ainst your table....I get this error...

You have an error in your SQL syntax; check the manual that corresponds to =
your MySQL server version for the right syntax to use near '(name, 1) betwe=
en '0' and '9'' at line 1

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Can a range be passed to a query?

On Aug 3, 2009, at 7:19 AM, Miller, Terion wrote:

> Not sure why none of these are working for me since you have them
> tested against your table....I get this error...

Hopefully I'm not speaking out of turn here, but did you by any
chance copy and paste that code from an e-mail? If so, there may be
invisible characters masquerading as spaces. I use TextWrangler (on a
Mac) and choose "Zap Gremlins..." so they show and then delete them.
That usually solves the problem for me.

Frank

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Can a range be passed to a query?

On 8/3/09 9:46 AM, "sono-io@fannullone.us" wrote:



On Aug 3, 2009, at 7:19 AM, Miller, Terion wrote:

> Not sure why none of these are working for me since you have them
> tested against your table....I get this error...

Hopefully I'm not speaking out of turn here, but did you by any
chance copy and paste that code from an e-mail? If so, there may be
invisible characters masquerading as spaces. I use TextWrangler (on a
Mac) and choose "Zap Gremlins..." so they show and then delete them.
That usually solves the problem for me.

Frank


Thanks Frank, but yep I'm using an editor that is good about such things, I=
'm not sure what the problem can be. And I get all the way to noon to figur=
e it out.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Can a range be passed to a query?(RESOLVED)

Here is is incase anyone else ever needs it.

//Create array with letters AND numb=
er sign $letters =3D range('A','Z'); =
array_push($letters, 'nums'); =
$menu =3D ''; $selectedLetter =3D isset($_=
GET['letter']) ? $_GET['letter'] : null; foreach($le=
tters as $letter) { if($l=
etter == $selectedLetter && $selectedLetter !=3D 'nums') =
{ $menu .=3D sprintf('%s ',=
$letter); } else if(=
$letter == $selectedLetter && $selectedLetter == 'nums') =
{ $menu .=3D sprintf('%s&nb=
sp', '#'); } else =
{ if($letter == =
'nums') { =
$menu .=3D sprintf(' ', 'num=
s', '#'); } e=
lse { $me=
nu .=3D sprintf(' ', $letter,=
$letter); } } =
} =
echo "

Re: Can a range be passed to a query?(RESOLVED)

--=-NUjomAc1vrym3Iz6DZWY
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Mon, 2009-08-03 at 09:33 -0700, Miller, Terion wrote:

> Here is is incase anyone else ever needs it.
>=20
> //Create array with letters AND nu=
mber sign $letters =3D range('A','Z'); =
array_push($letters, 'nums'); =
$menu =3D ''; $selectedLetter =3D isset(=
$_GET['letter']) ? $_GET['letter'] : null; foreach($=
letters as $letter) { if(=
$letter == $selectedLetter && $selectedLetter !=3D 'nums') =
{ $menu .=3D sprintf('%s =
', $letter); } else i=
f($letter == $selectedLetter && $selectedLetter == 'nums') =
{ $menu .=3D sprintf('%s&=
nbsp', '#'); } else =
{ if($letter =3D=
=3D 'nums') { =
$menu .=3D sprintf(' ', =
'nums', '#'); } =
else { =
$menu .=3D sprintf(' ', $let=
ter, $letter); } =
} } =
echo "

Re: Can a range be passed to a query?(RESOLVED)

Sorry group, I keep thinking they are real text...I'm new on a iMac and Ent=
ourage...



On 8/3/09 2:13 PM, "Ashley Sheridan" wrote:

On Mon, 2009-08-03 at 09:33 -0700, Miller, Terion wrote:

Here is is incase anyone else ever needs it.

//Create array with letters AND numb=
er sign $letters =3D range('A','Z'); =
array_push($letters, 'nums'); =
$menu =3D ''; $selectedLetter =3D isset($_=
GET['letter']) ? $_GET['letter'] : null; foreach($le=
tters as $letter) { if($l=
etter == $selectedLetter && $selectedLetter !=3D 'nums') =
{ $menu .=3D sprintf('%s ',=
$letter); } else if(=
$letter == $selectedLetter && $selectedLetter == 'nums') =
{ $menu .=3D sprintf('%s&nb=
sp', '#'); } else =
{ if($letter == =
'nums') { =
$menu .=3D sprintf(' ', 'num=
s', '#'); } e=
lse { $me=
nu .=3D sprintf(' ', $letter,=
$letter); } } =
} =
echo "